package com.uinnova.product.eam.web.diagram.bean.impt;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.binary.core.exception.MessageException;
import com.binary.core.io.Resource;
import com.binary.core.io.support.FileResource;
import com.binary.core.lang.Conver;
import com.binary.core.util.BinaryUtils;
import com.binary.framework.Local;
import com.binary.framework.bean.annotation.Comment;
import com.binary.framework.exception.ServiceException;
import com.binary.framework.web.LocalSpace;
import com.binary.jdbc.Page;
import com.uinnova.product.vmdb.comm.i18n.MessageUtil;
import com.uinnova.product.vmdb.comm.i18n.VerifyType;
import com.uinnova.product.vmdb.comm.model.ci.CcCiAttrDef;
import com.uinnova.product.vmdb.comm.model.ci.CcCiClass;
import com.uinnova.product.vmdb.comm.model.ci.CcFixAttrMapping;
import com.uinnova.product.vmdb.comm.util.CommUtil;
import com.uinnova.product.vmdb.comm.util.ExcelCovertCSVReaderUtil;
import com.uinnova.product.vmdb.comm.util.XLSXCovertCSVReader;
import com.uinnova.product.vmdb.provider.ci.bean.CcCiClassInfo;

@Slf4j
public class CiExcelBatchImportUtil {
	
	public static class SheetTitle {
		@Comment("sheet页名称")
		public String sheetName;
		@Comment("sheet页第一行的title信息")
		public List<String> titles;
		public SheetTitle() {}
		public SheetTitle(String name, List<String> titles) {
			this.sheetName=name;
			this.titles=titles;
		}
		public String getSheetName() {
			return sheetName;
		}
		public void setSheetName(String sheetName) {
			this.sheetName = sheetName;
		}
		public List<String> getTitles() {
			return titles;
		}
		public void setTitles(List<String> titles) {
			this.titles = titles;
		}
	}
	
	public static class CiExcelResult {
		@Comment("临时文件名")
		private String fileName;
		@Comment("源文件名")
		private String originalFileName;


		@Comment("临时文件夹名")
		private String dirName;
		@Comment("sheet页名列表")
		private List<String> SheetNames;
		@Comment("当前sheet页")
		private String curSheetName;
		@Comment("页号")
		private long pageNum;
		@Comment("页大小")
		private long pageSize;
		@Comment("总记录")
		private long totalRows;
		@Comment("分类属性名")
		private List<String> fieldNames;
		@Comment("页标题")
		private List<SheetTitle> sheetTitles;
		@Comment("内容")
		private List<Map<Integer,String>> datas;
		
		public String getFileName() {
			return fileName;
		}
		public void setFileName(String fileName) {
			this.fileName = fileName;
		}
		public String getOriginalFileName() {
			return originalFileName;
		}
		public void setOriginalFileName(String originalFileName) { this.originalFileName = originalFileName; }
		public String getDirName() { return dirName; }
		public void setDirName(String dirName) { this.dirName = dirName; }
		public List<String> getSheetNames() {
			return SheetNames;
		}
		public void setSheetNames(List<String> sheetNames) {
			SheetNames = sheetNames;
		}
		public String getCurSheetName() {
			return curSheetName;
		}
		public void setCurSheetName(String curSheetName) {
			this.curSheetName = curSheetName;
		}
		public long getPageNum() {
			return pageNum;
		}
		public void setPageNum(long pageNum) {
			this.pageNum = pageNum;
		}
		public long getPageSize() {
			return pageSize;
		}
		public void setPageSize(long pageSize) {
			this.pageSize = pageSize;
		}
		public long getTotalRows() {
			return totalRows;
		}
		public void setTotalRows(long totalRows) {
			this.totalRows = totalRows;
		}
		public List<String> getFieldNames() {
			return fieldNames;
		}
		public void setFieldNames(List<String> fieldNames) {
			this.fieldNames = fieldNames;
		}
		public List<SheetTitle> getSheetTitles() {
			return sheetTitles;
		}
		public void setSheetTitles(List<SheetTitle> sheetTitles) {
			this.sheetTitles = sheetTitles;
		}
		public List<Map<Integer, String>> getDatas() {
			return datas;
		}
		public void setDatas(List<Map<Integer, String>> datas) {
			this.datas = datas;
		} 
		
	}
	
	public static class CiExcelCdt {
		public static final Integer IMPORT_TYPE_UPDATE=0;
		public static final Integer IMPORT_TYPE_OVERRIDE=1;
		@Comment("临时文件名")
		private String fileName;
//		private String originalFileName;
		@Comment("临时目录名")
		private String dirName;
		@Comment("sheet页名")
		private String sheetName;
		@Comment("回显Excel数据时使用的页码")
		private Integer pageNum;
		@Comment("回显Excel数据时使用的一页容量")
		private Integer pageSize;
		@Comment("对应的ci分类信息")
		private CcCiClassInfo ciClassInfo;
		@Comment("对应的ci分类id")
		private Long ciClassId;
		@Comment("新增分类类型时,记录classCode")
		private String classCode;
		@Comment("ciClass是否更新")
		private Boolean isCiClassUpdate;
		@Comment("增量为0, 全量为1")
		private Integer importType=0;
		@Comment("excle中字段位置(从左到右0,1,2...)对应分类的字段名字,没有匹配则为null")
		private Map<String, String> fieldMap;
		
		
		public Long getCiClassId() {
			return ciClassId;
		}
		public void setCiClassId(Long ciClassId) {
			this.ciClassId = ciClassId;
		}
		public String getFileName() {
			return fileName;
		}
		public void setFileName(String fileName) {
			this.fileName = fileName;
		}
		public String getDirName() { return dirName; }
		public void setDirName(String dirName) { this.dirName = dirName; }
//		public String getOriginalFileName() {
//			return originalFileName;
//		}
//		public void setOriginalFileName(String originalFileName) {
//			this.originalFileName = originalFileName;
//		}
		public String getSheetName() {
			return sheetName;
		}
		public void setSheetName(String sheetName) {
			this.sheetName = sheetName;
		}
		public Integer getPageNum() {
			return pageNum;
		}
		public void setPageNum(Integer pageNum) {
			this.pageNum = pageNum;
		}
		public Integer getPageSize() {
			return pageSize;
		}
		public void setPageSize(Integer pageSize) {
			this.pageSize = pageSize;
		}
		public CcCiClassInfo getCiClassInfo() {
			return ciClassInfo;
		}
		public void setCiClassInfo(CcCiClassInfo ciClassInfo) {
			this.ciClassInfo = ciClassInfo;
		}
		public String getClassCode() {
			return classCode;
		}
		public void setClassCode(String classCode) {
			this.classCode = classCode;
		}
		public Boolean getIsCiClassUpdate() {
			return isCiClassUpdate;
		}
		public void setIsCiClassUpdate(Boolean isCiClassUpdate) {
			this.isCiClassUpdate = isCiClassUpdate;
		}
		public Map<String, String> getFieldMap() {
			return fieldMap;
		}
		public void setFieldMap(Map<String, String> fieldMap) {
			this.fieldMap = fieldMap;
		}
		public Integer getImportType() {
			return importType;
		}
		public void setImportType(Integer importType) {
			this.importType = importType;
		}
		
	}
	
	/**
	 * 接受 Excel 字段映射条件
	 * @author uinnova
	 *
	 */
	public static class CiExcelSaveCdt{
		@Comment("接受同一类 ciClass 信息")
		private ExcelCcCiClassInfo ciClassInfo;
		@Comment("获得同一个分类的 各个 sheet页的映射信息")
		private List<CiExcelCdt> cdts;
		
		public ExcelCcCiClassInfo getCiClassInfo() {
			return ciClassInfo;
		}
		public void setCiClassInfo(ExcelCcCiClassInfo ciClassInfo) {
			this.ciClassInfo = ciClassInfo;
		}
		public List<CiExcelCdt> getCdts() {
			return cdts;
		}
		public void setCdts(List<CiExcelCdt> cdts) {
			this.cdts = cdts;
		}
		
	}
	
	/**
	 * Excel导入时 保留ci分类 旧属性定义的接受格式
	 * @author uinnova
	 *
	 */
	public static class ExcelCcCiClassInfo extends CcCiClassInfo{
		private static final long serialVersionUID = 1L;
		@Comment("旧属性定义")
		private List<CcCiAttrDef> oldAttrDefs;

		public List<CcCiAttrDef> getOldAttrDefs() {
			return oldAttrDefs;
		}
		public void setOldAttrDefs(List<CcCiAttrDef> oldAttrDefs) {
			this.oldAttrDefs = oldAttrDefs;
		}
	}
	
	public static class SheetsDatasCdt{
		private String fileSheetName;
		private List<Map<String, String>> datas;
		private List<String> titles;
		
		public List<String> getTitles() {
			return titles;
		}
		public void setTitles(List<String> titles) {
			this.titles = titles;
		}
		public String getFileSheetName() {
			return fileSheetName;
		}
		public void setFileSheetName(String fileSheetName) {
			this.fileSheetName = fileSheetName;
		}
		public List<Map<String, String>> getDatas() {
			return datas;
		}
		public void setDatas(List<Map<String, String>> datas) {
			this.datas = datas;
		}
	}

	public static String getSheetDataFromJson(String userCode, String fileName, String sheetName) {
		String result = "";
		File file = new File(Local.getTmpSpace() + "/" + fileName, sheetName);
		if (file.isFile() && file.exists()) {
			try (InputStreamReader isr = new InputStreamReader(Files.newInputStream(file.toPath()), StandardCharsets.UTF_8);
				 BufferedReader br = new BufferedReader(isr)){
				String lineTxt;
				while ((lineTxt = br.readLine()) != null) {
					result = lineTxt;
				}
			} catch (Exception e) {
				System.out.println("文件读取错误!");
			}
		} else {

		}

		return result;
	}
	
	/**
	 * 分页获取Excel指定的Sheet页内容(分Excel版本读取)
	 * @param pageNum
	 * @param pageSize
	 * @param userCode
	 * @param fileName
	 * @param sheetName
	 * @return
	 */
	public static CiExcelResult getSheetData(Integer pageNum, Integer pageSize, String userCode, String fileName, String sheetName){
		MessageUtil.checkEmpty(fileName, "fileName");
		MessageUtil.checkEmpty(sheetName, "className");
		if(pageSize > 500) pageSize = 500;
		CiExcelResult result = new CiExcelResult();
		
		log.debug("【start read excel datas...】");
		log.debug("【excel name "+fileName+"】");
		//分文件类型读取
		if(fileName.toLowerCase().endsWith(CommUtil.EXCEL03_XLS_EXTENSION)) {
			Workbook workbook = null;
			try{
				workbook = getWorkbook(userCode, fileName);
				Sheet sheet = getSheet(workbook, sheetName);
				Map<Integer, String> sheetTitleMap = getSheetTitles(sheet);
				List<String> sheetTitles = new ArrayList<String>(sheetTitleMap.values());
				Set<Integer> sheetTitleNums = sheetTitleMap.keySet();
				Page<Map<Integer,String>> pageData = getSheetPageDatas(pageNum, pageSize, sheet, sheetTitleNums);
				
				result.setFieldNames(sheetTitles);
				result.setDatas(pageData.getData());
				result.setPageNum(pageData.getPageNum());
				result.setPageSize(pageData.getPageSize());
				result.setTotalRows(pageData.getTotalRows());
				result.setFileName(fileName);
				result.setCurSheetName(sheetName);
				return result;			
			} catch(Exception exp){
				log.error("",exp);
				return result;
			} finally{
				if(workbook!=null){
					try {
						workbook.close();
					} catch (IOException e) {
					}
				}			
			}
			
		} else if(fileName.toLowerCase().endsWith(CommUtil.EXCEL07_XLSX_EXTENSION)) {
			try {
				File file = getFile(userCode, fileName);
				List<String[]> pageList = XLSXCovertCSVReader.readerExcelPageByFile(pageNum, pageSize, file, sheetName);
				
				int totalRows = 0;
				if(pageList != null && pageList.size() > 0) {
					//获取总行数
					String[] remove = pageList.remove(pageList.size()-1);
					if(remove != null && remove.length == 1) totalRows = Conver.to(remove[0], int.class);
					
					//获取标题内容
					List<String> sheetTitles = ExcelCovertCSVReaderUtil.getTitlesBySheetDatas(pageList);
					
					//获取正文内容
					List<Map<Integer, String>> maps = XLSXCovertCSVReader.arrayConvertIdxMapsBySheetDatas(pageList);
					
					result.setFieldNames(sheetTitles);
					result.setDatas(maps);
				} else {
					//防止页面不校验非空报错
					result.setFieldNames(new ArrayList<String>());
					result.setDatas(new ArrayList<Map<Integer, String>>());
				}
				
				result.setPageSize(pageSize);
				result.setPageNum(pageNum);
				result.setTotalRows(totalRows);
				result.setFileName(fileName);
				result.setCurSheetName(sheetName);
				
			} catch (Exception e) {
				log.error("", e);
				return result;
			}
			
		}
		
		log.debug("【end read excel datas...】");
		
		return result;
		
	}
	
	/**
	 * 获取Excel全部正文信息(已分Excel类型读取)
	 * @param userCode
	 * @param fileName
	 * @param sheetName
	 * @return Map集合,Map的Key为索引
	 */
	public static List<Map<Integer, String>> getSheetDatas(String userCode, String fileName, String sheetName){
		MessageUtil.checkEmpty(fileName, "fileName");
		MessageUtil.checkEmpty(sheetName, "className");
		
		List<Map<Integer, String>> sheetDatas = new ArrayList<Map<Integer, String>>();
		
		//分文件类型读取
		if(fileName.toLowerCase().endsWith(CommUtil.EXCEL03_XLS_EXTENSION)) {
			Workbook workbook = null;
			try{
				workbook = getWorkbook(userCode, fileName);
				Sheet sheet = getSheet(workbook, sheetName);
				Map<Integer, String> sheetTitleMap = getSheetTitles(sheet);
				Set<Integer> sheetTitleNums = sheetTitleMap.keySet();
				
				Integer pageNum = 1; 
				Integer pageSize = 5000;
				while (true) {
					Page<Map<Integer,String>> pageData = getSheetPageDatas(pageNum, pageSize, sheet, sheetTitleNums);
					List<Map<Integer,String>> datas = pageData.getData();
					if(datas != null && datas.size() > 0) {
						sheetDatas.addAll(datas);
						pageNum++;
					} else {
						break;
					}
				}
				
				return sheetDatas;			
			} catch(Exception exp){
				log.error("",exp);
				return sheetDatas;
			} finally{
				if(workbook!=null){
					try {
						workbook.close();
					} catch (IOException e) {
					}
				}			
			}
			
		} else if(fileName.toLowerCase().endsWith(CommUtil.EXCEL07_XLSX_EXTENSION)) {
			try {
				File file = getFile(userCode, fileName);
				List<String[]> pageList = XLSXCovertCSVReader.readerExcelByFile(file, sheetName, 0);
				//获取正文内容
				List<Map<Integer, String>> datas = XLSXCovertCSVReader.arrayConvertIdxMapsBySheetDatas(pageList);
				if(datas != null && datas.size() > 0) sheetDatas.addAll(datas);
			} catch (Exception e) {
				log.error("", e);
				return sheetDatas;
			}
			
		}
		return sheetDatas;
		
	}

	
	
	
	public static List<Map<String,String>> getSheetData(String userCode, CiExcelCdt ciExcelCdt){
		String sheetName = ciExcelCdt.getSheetName();
		String fileName = ciExcelCdt.getFileName();
		Workbook workbook = getWorkbook(userCode, fileName);
		Sheet sheet = getSheet(workbook, sheetName);
		
		Map<String, String> fieldMap = ciExcelCdt.getFieldMap();
//		if (BinaryUtils.isEmpty(fieldMap)) {
//			//没有属性映射, 取出全部数据
//			return getSheetDatas(sheet);
//		}
//		//有属性映射时的获取数据方式.
		return getSheetDatas(sheet, fieldMap);
	}


	public static CiExcelResult getSheetNames(String userCode, String fileName) {
		Workbook workbook = getWorkbook(userCode, fileName);
		List<String> sheetNames = getSheetNames(workbook);
		
		CiExcelResult result = new CiExcelResult();
		result.setSheetNames(sheetNames);
		
		return result;
	}
	
	/**
	 * 保存文件,并生成包含临时文件名和sheetname集合的返回结果
	 * @param userCode
	 * @param excelInputStream
	 * @return
	 */ //保存 rlt
	public static CiExcelResult saveExcel(String userCode, InputStream excelInputStream, String excelType) {
		File file = saveFile(userCode, excelInputStream, excelType);
	
		CiExcelResult result = new CiExcelResult();
		result.setFileName(file.getName());

		File fileDir = new File(Local.getTmpSpace(), BinaryUtils.getUUID());
		fileDir.mkdirs();
		result.setDirName(fileDir.getName());
		//保存文件并返回信息
		resultAddSheetsTitles(userCode,file.getName(), result, excelType, fileDir);
		try {
			excelInputStream.close();
		} catch (IOException e) {
		}
		return result;
	}
	
	/**
	 * 保存文件,并生成包含临时文件名和sheet的name和title
	 * @param userCode
	 * @param excelInputStream
	 * @return
	 */
	public static CiExcelResult saveExcelGetTitles(String userCode, InputStream excelInputStream, String excelType) {
		try{
			log.debug("【start save excel...】");
			File file = saveFile(userCode, excelInputStream, excelType);
			log.debug("【end save excel...】");
			log.debug("【excel file path: "+file.getPath()+"】");
			
			CiExcelResult result = new CiExcelResult();
			result.setFileName(file.getName());

			log.debug("【start read excel sheetName and title...】");

			File fileDir = new File(Local.getTmpSpace(), BinaryUtils.getUUID());
			fileDir.mkdirs();
			result.setDirName(fileDir.getName());
			//读取Excel中Sheet页名字以及Sheet下的标题行
			resultAddSheetsTitles(userCode, file.getName(), result, excelType, fileDir);
			log.debug("【end read excel sheetName and title...】");
			
			return result;
		} catch (Exception exp){
			log.error("",exp);
			throw BinaryUtils.transException(exp, ServiceException.class);
		} finally{
			try {
				excelInputStream.close();
			} catch (IOException e) {
				throw BinaryUtils.transException(e, ServiceException.class);
			}		
		}
	}

	
	/**
	 * 获取Excel所有的Sheet页名字和标题行类容
	 * @param userCode
	 * @param fileName
	 * @param result
	 * @param excelType
	 */
	private static void resultAddSheetsTitles(String userCode, String fileName, CiExcelResult result, String excelType, File fileDir) {
		//分文件类型读取
		if(excelType.toLowerCase().equals(CommUtil.EXCEL03_XLS_EXTENSION)) {
			Workbook workbook = null;
			try{
				workbook = getWorkbook(userCode, fileName);
				List<SheetTitle> sheetsTitles = new ArrayList<SheetTitle>();
				if (!BinaryUtils.isEmpty(workbook)) {
					List<String> realSheetNames = new ArrayList<>();
					List<String> sheetNames = getSheetNames(workbook);
					for (String name : sheetNames) {
						//获取每页的标题行内容
						Sheet sheet = workbook.getSheet(name);
						Map<Integer, String> sheetTitleMap = getSheetTitles(sheet);
						List<String> sheetTitlesList = new ArrayList<String>(sheetTitleMap.values());
						Set<Integer> sheetTitleNums = sheetTitleMap.keySet();
						Page<Map<Integer, String>> pageData = getSheetPageDatas(1, 800, sheet, sheetTitleNums);
						if (pageData != null && pageData.getData().size() != 0) {
							XLSXCovertCSVReader.saveFileTemp(fileDir, name, sheetTitlesList, pageData);
							realSheetNames.add(name);
							SheetTitle sheetTitle = new SheetTitle(name,sheetTitlesList);
							sheetsTitles.add(sheetTitle);
						}
					}
					result.setSheetNames(realSheetNames);
				}
				result.setSheetTitles(sheetsTitles);
			}catch(Exception exp){
				log.error("",exp);
				throw BinaryUtils.transException(exp, ServiceException.class);
			}finally{
				if(workbook!=null){
					try {
						workbook.close();
					} catch (IOException e) {
						log.error("",e);
						throw BinaryUtils.transException(e, ServiceException.class);
					}
				}
			}
			
		} else if (excelType.toLowerCase().equals(CommUtil.EXCEL07_XLSX_EXTENSION)) {
			try {
				File file = getFile(userCode, fileName);
				if(!file.exists()) throw MessageException.i18n("BS_MDOMAIN_NOTFOUNDFILE");

				File dir = new File(Local.getTmpSpace(), BinaryUtils.getUUID());
				dir.mkdirs();

				//获取每页的sheet名字和标题行内容
				List<Map<String, List<String>>> sheets = XLSXCovertCSVReader.readerSheetTitlesByFile(file, fileDir);
				List<SheetTitle> sheetTitles = new ArrayList<SheetTitle>();
				List<String> sheetNames = new ArrayList<>();
				if(sheets != null && sheets.size() > 0) {
					for (Map<String, List<String>> stMap : sheets) {
						if(stMap == null || stMap.isEmpty()) continue;
						
						Set<String> keys = stMap.keySet();
						if(keys == null || keys.isEmpty()) continue;
						
						String[] keyStrs = keys.toArray(new String[keys.size()]);
						String sheetName = keyStrs[0];
						if(BinaryUtils.isEmpty(sheetName)) continue;
						List<String> titiles = stMap.get(sheetName);
						if(BinaryUtils.isEmpty(titiles)) titiles = new ArrayList<String>();
						
						SheetTitle sheetTitle = new SheetTitle();
						sheetTitle.setSheetName(sheetName);
						sheetTitle.setTitles(titiles);
						sheetTitles.add(sheetTitle);

						sheetNames.add(sheetName);
					}

				}
				result.setSheetNames(sheetNames);
				result.setSheetTitles(sheetTitles);
				
			} catch (Exception e) {
				throw BinaryUtils.transException(e, ServiceException.class);
			}
			
		}
		
	}
	
	

	private static void resultAddSheetsNames(String userCode, String fileName, CiExcelResult result, String excelType, File dir) {
		//分文件类型读取
		if(excelType.toLowerCase().equals(CommUtil.EXCEL03_XLS_EXTENSION)) {
			Workbook workbook = null;
			try {
				workbook = getWorkbook(userCode,fileName);
				List<String> sheetNames = getSheetNames(workbook);
				result.setSheetNames(sheetNames);
				
			} catch (Exception e) {
				throw BinaryUtils.transException(e, ServiceException.class);
			} finally {
				if(workbook != null) {
					try {
						workbook.close();
					} catch (IOException e) {
						throw BinaryUtils.transException(e, ServiceException.class);
					}
				}
			}
			
		} else if (excelType.toLowerCase().equals(CommUtil.EXCEL07_XLSX_EXTENSION)) {
			try {
				File file = getFile(userCode, fileName);
				if(!file.exists()) throw MessageException.i18n("BS_MDOMAIN_NOTFOUNDFILE");
				
				List<String> sheetNames = XLSXCovertCSVReader.readerSheetNamesByFile(file);
				if(BinaryUtils.isEmpty(sheetNames)) sheetNames = new ArrayList<String>();
				result.setSheetNames(sheetNames);
				
			} catch (Exception e) {
				throw BinaryUtils.transException(e, ServiceException.class);
			}
			
		}
		
		
	}
	
	
	
	public static Resource getFileResource(String local, String fileName){
		File file = getFile(local, fileName);
		try {
			return new FileResource(file);
		} catch (Exception e) {
			MessageUtil.throwVerify(VerifyType.NOT_EXIST, "file", fileName);
		} 
		return null;
	}
	
	public static InputStream getInputStream(String local,String fileName){
		File file = getFile(local, fileName);
		try {
			return new FileInputStream(file);
		} catch (FileNotFoundException e) {
			MessageUtil.throwVerify(VerifyType.NOT_EXIST, "file", fileName);
		} 
		return null;
	}
	
	private static Workbook getWorkbook(File file) {
		try {
			if(file.getName().toLowerCase().endsWith(".xls")){
				FileInputStream input = new FileInputStream(file);
				return new HSSFWorkbook(input);
			} else if(file.getName().toLowerCase().endsWith(".xlsx")){
				FileInputStream input = new FileInputStream(file);
				return new XSSFWorkbook(input);
			} else{
				return WorkbookFactory.create(file);
			}			
		} catch (Exception e) {
			log.error(file.getAbsolutePath(), e);
			throw BinaryUtils.transException(e, ServiceException.class);
		}			
	}
		
	
	private static Sheet getSheet(Workbook workbook,String sheetName) {
		if(workbook == null) return null;
		return workbook.getSheet(sheetName);
	}
	
	private static List<String> getSheetNames(Workbook workbook){
		List<String> sheetNames = new ArrayList<String>();
		int numberOfSheets = workbook.getNumberOfSheets();
		for (int i = 0; i < numberOfSheets; i++) {
			sheetNames.add(workbook.getSheetName(i));
		}
		return sheetNames;
	}
	
	public static List<String> getSheetTitlesListFirstIsBlank(Sheet sheet) {
		Map<Integer, String> sheetTitles = getSheetTitles(sheet);
		List<String> titles = new ArrayList<String>(sheetTitles.size());
		titles.add("");
		titles.addAll(sheetTitles.values());
		return titles;
	}
	private static Map<Integer, String> getSheetTitles(Sheet sheet) {
		LinkedHashMap<Integer, String> ret = new LinkedHashMap<Integer, String>();
		Row titleRow = sheet.getRow(0);
		if(titleRow == null){
			return ret;
		}

		short lastCellNum = titleRow.getLastCellNum();
		
		for (int i = 0; i < lastCellNum; i++) {
			Cell cell = titleRow.getCell(i);
			String title = getStringValue(cell);
			if(!BinaryUtils.isEmpty(title, true)){
				ret.put(i, title);
			}
		}
		
		return ret;
	}

	public static Page<Map<Integer,String>> getSheetPageDatas(Integer pageNum, Integer pageSize, Sheet sheet, Collection<Integer> sheetTitleNums) {
		Page<Map<Integer, String>> page = new Page<Map<Integer,String>>();
		
		List<Map<Integer, String>> datas = new ArrayList<Map<Integer,String>>();
		pageNum = pageNum == null || pageNum < 1 ? 1:pageNum;
		pageSize = pageSize == null || pageSize < 1 ? 20 : pageSize;
		
		page.setData(datas);
		page.setPageNum(pageNum);
		page.setPageSize(pageSize);
		
		if (sheet == null) {
			return page;
		}
		
		Row titleRow = sheet.getRow(0);
		if(titleRow == null){
			return page;
		}
		short lastCellNum = titleRow.getLastCellNum();

		int lastRowNum = sheet.getLastRowNum();//结果是基于1的 列号
		int start = (pageNum - 1) * pageSize + 1;
		int end = start + pageSize;
		if(end > lastRowNum) end = lastRowNum+1;// 表中最后一条数据大于end时, 重新给end赋值,因 0行是title, 数据从1开始, 所以应+1
		
		for (; start < end; start++) { 
			Map<Integer,String> data = new HashMap<Integer, String>();
			Row row = sheet.getRow(start);
			if(row != null && !isBlankRow(row)) {
				//定义计数器, i-counter 作为datas的key, key的取值一般情况下与列索引相同, 只有当存在title没有定义的情况时, key与列索引不同
				int counter = 0;
				for (int i = 0; i < lastCellNum; i++) {
					// 开始列遍历, i表示列索引, i小于列定义上限
					if (!BinaryUtils.isEmpty(sheetTitleNums) && !sheetTitleNums.contains(i)) {
						// 本列title没有定义时跳过此单元格
						++counter;
						continue;
					}
					Cell cell = row.getCell(i);
					if(!BinaryUtils.isEmpty(cell)) {
						CellType cellType = cell.getCellType();
						if(CellType.NUMERIC == cellType){
							if(DateUtil.isCellDateFormatted(cell)){
								Date d = cell.getDateCellValue();
								long time = d.getTime();
								
								String v = Conver.to(d, String.class, time>1500000000000l?"yyyy-MM-dd HH:mm:ss":"HH:mm:ss");
								int idx = v.indexOf(' ');
								if(idx>0 && v.endsWith("00:00:00")) {
									v = v.substring(0, idx);
								}
								cell.setCellValue(v);
							}
						}
					}
					String cellDate = BinaryUtils.isEmpty(cell) ? "" :getStringValue(cell);
					data.put(i-counter, cellDate);
				}
				datas.add(data);
			}
		}
		page.setPageNum(pageNum);
		page.setPageSize(pageSize);
		page.setTotalRows(sheet.getPhysicalNumberOfRows()-1);
		
		return page;
	}
	
	/**
	 * 判断 row 的数据是否为空
	 * @param row
	 * @return
	 */
	public static boolean isBlankRow(Row row){
        if(row == null) return true;
        boolean result = true;
        for(int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++){
            Cell cell = row.getCell(i, MissingCellPolicy.RETURN_BLANK_AS_NULL);
            
            
            String value = "";
            if(cell != null){
            	CellType cellType = cell.getCellType();
            	if(cellType == CellType.STRING){
            		value = cell.getStringCellValue();
            	} else if(cellType == CellType.NUMERIC){
            		value = String.valueOf((int) cell.getNumericCellValue());
            	} else if(cellType == CellType.BOOLEAN){
            		value = String.valueOf(cell.getBooleanCellValue());
            	} else if(cellType == CellType.FORMULA){
            		value = String.valueOf(cell.getCellFormula());
            	} else {
            		value = cell.getStringCellValue();
            	}
            	
//                switch (cellType) {
//                case CellType.STRING:
//                    value = cell.getStringCellValue();
//                    break;
//                case CellType.NUMERIC:
//                    value = String.valueOf((int) cell.getNumericCellValue());
//                    break;
//                case Cell.CELL_TYPE_BOOLEAN:
//                    value = String.valueOf(cell.getBooleanCellValue());
//                    break;
//                case Cell.CELL_TYPE_FORMULA:
//                    value = String.valueOf(cell.getCellFormula());
//                    break;
//                //case Cell.CELL_TYPE_BLANK:
//                //    break;
//                default:
//                    break;
//                }
                 
                if(!value.trim().equals("")){
                    result = false;
                    break;
                }
            }
        }
         
        return result;
    }
	
	public static List<Map<String,String>> getSheetDatas(Sheet sheet, Map<String, String> fieldMap) {
		List<Map<String,String>> ret = new ArrayList<Map<String,String>>();
		if (sheet == null) {
			return ret;
		}
		//防止存在空列时的映射错误, 获得titleNums
		Map<Integer, String> sheetTitleMap = getSheetTitles(sheet);
		Set<Integer> titleNums = sheetTitleMap.keySet();
		
		int lastRowNum = sheet.getLastRowNum();
		for (int i = 1; i <= lastRowNum; i++) {
			Row row = sheet.getRow(i);
			if(row != null && !isBlankRow(row)) {
				Map<String,String> data = new LinkedHashMap<String, String>();
				//计数器j用于从fieldMap中取映射关系
				int j=0;
				for (Integer titleNum : titleNums) {
					String key;
					if (BinaryUtils.isEmpty(fieldMap)) {
						//没有fieldMap时返回默认的 title
						key = sheetTitleMap.get(titleNum);
					}else {
						key = fieldMap.get(""+j);
					}
					
					++j;
					if(!BinaryUtils.isEmpty(key)) {
						//此key为ci属性名, value为从Excel中读取的值
						data.put(key, getStringValue(row.getCell(titleNum)));
					}
				}
				ret.add(data);
			}
		}
		return ret;
	}
	
	public static List<Map<String,String>> getSheetDatasbyPage(int pageNum,int pageSize,Sheet sheet, Map<String, String> fieldMap) {
		List<Map<String,String>> ret = new ArrayList<Map<String,String>>();
		if (sheet == null) {
			return ret;
		}
		//防止存在空列时的映射错误, 获得titleNums
		Map<Integer, String> sheetTitleMap = getSheetTitles(sheet);
		Set<Integer> titleNums = sheetTitleMap.keySet();
		
		int lastRowNum = sheet.getLastRowNum();
		int starRow = (pageNum-1)*pageSize+1;
		int endRow = pageNum*pageSize;
		endRow = endRow>lastRowNum?lastRowNum:endRow;
		
		for (int i = starRow; i <= endRow; i++) {
			Row row = sheet.getRow(i);
			if(row != null && !isBlankRow(row)) {
				Map<String,String> data = new LinkedHashMap<String, String>();
				//计数器j用于从fieldMap中取映射关系
				int j=0;
				for (Integer titleNum : titleNums) {
					String key;
					if (BinaryUtils.isEmpty(fieldMap)) {
						//没有fieldMap时返回默认的 title
						key = sheetTitleMap.get(titleNum);
					}else {
						key = fieldMap.get(""+j);
					}
					
					++j;
					if(!BinaryUtils.isEmpty(key)) {
						//此key为ci属性名, value为从Excel中读取的值
						data.put(key, getStringValue(row.getCell(titleNum)));
					}
				}
				ret.add(data);
			}
		}
		return ret;
	}
	
	
	
	
	private static File saveFile(String local,InputStream is, String fileExd){
		LocalSpace.getRoot();
		File space = LocalSpace.getSpace(local);
		if(!space.exists()) space.mkdirs();
		
		String fileName = BinaryUtils.getUUID() + fileExd;
		File file = new File(space, fileName);

		FileOutputStream output = null;
		try {
			output = new FileOutputStream(file);
			IOUtils.copy(is, output);
		} catch (IOException e) {
			throw MessageException.i18n("BS_MNAME_SAVE_FAIL");
		}finally {
			if(output != null) {
				try {
					output.close();
				} catch (IOException e) {
					throw BinaryUtils.transException(e, ServiceException.class);
				}
			}
		}
		return file;
	}
	
	public static File getFile(String local,String fileName){
		LocalSpace.getRoot();
		File space = LocalSpace.getSpace(local);
		return new File(space, fileName);
	}
	
	
	private static Object getValue(Cell cell){
		if(cell == null){
			return null;
		}
		CellType cellType = cell.getCellType();
		
		if(CellType.STRING == cellType){
			return cell.getStringCellValue(); 
		} else if(CellType.NUMERIC == cellType){
			Double doubleValue = Double.valueOf(cell.getNumericCellValue());
			return doubleValue - doubleValue.intValue() == 0? doubleValue.intValue()+"":doubleValue+"";			
		} else if(CellType.BOOLEAN == cellType){
			return Boolean.valueOf(cell.getBooleanCellValue());
		} else if(CellType.BLANK == cellType){
			return "";
		} else {
			try {
				return cell.getDateCellValue();
			}catch(Exception e) {
				return cell.toString();
			}			
		}
		
//		switch (cell.getCellType()) { 
//			case HSSFCell.CELL_TYPE_STRING: return cell.getStringCellValue(); 
//			case HSSFCell.CELL_TYPE_NUMERIC: 
//				Double doubleValue = Double.valueOf(cell.getNumericCellValue());
//				return doubleValue - doubleValue.intValue() == 0? doubleValue.intValue()+"":doubleValue+"";
//			case HSSFCell.CELL_TYPE_BOOLEAN: return Boolean.valueOf(cell.getBooleanCellValue());
//			case HSSFCell.CELL_TYPE_BLANK: return "";
//			default: {
//				try {
//					return cell.getDateCellValue();
//				}catch(Exception e) {
//					return cell.toString();
//				}
//			}
//		}
	}
	
	public static String getStringValue(Cell cell){
		Object value = getValue(cell);
		if(value == null){
//			return (String) value;
			return "";
		}
		return value.toString().trim();
	}

	/**
	 * 将分类属性定义去重合并
	 * @param ciExcelSaveCdt
	 * @return
	 */
	public static CcCiClassInfo mergingSameAttrs(CiExcelSaveCdt ciExcelSaveCdt) {
		ExcelCcCiClassInfo ciClassInfo = ciExcelSaveCdt.getCiClassInfo();
		MessageUtil.checkEmpty(ciClassInfo, "ciExcelSaveCdts.ciClassInfo");
		
		CcCiClass ciClass = ciClassInfo.getCiClass();
		List<CcCiAttrDef> attrDefs = ciClassInfo.getAttrDefs();
		CcFixAttrMapping fixMapping = ciClassInfo.getFixMapping();
		MessageUtil.checkEmpty(ciClass, "ciExcelSaveCdts.ciClassInfo.ciClass");
		MessageUtil.checkEmpty(attrDefs, "ciExcelSaveCdts.ciClassInfo.attrDefs");
		MessageUtil.checkEmpty(fixMapping, "ciExcelSaveCdts.ciClassInfo.fixMapping");
		
		// 存储属性名,用来去重
		HashMap<String, CcCiAttrDef> exsitAttrMap = new HashMap<String, CcCiAttrDef>();
		//获得旧的属性定义
		List<CcCiAttrDef> oldAttrDefs = ciClassInfo.getOldAttrDefs();
		// 新属性排序起始值
		int orderNo = 1;
		// 新增分类不要旧属性
		if (!BinaryUtils.isEmpty(oldAttrDefs)) {
			// 非新增分类准备排序
			for (CcCiAttrDef ccCiAttrDef : oldAttrDefs) {
				String proName = ccCiAttrDef.getProName();
				ccCiAttrDef.setIsMajor(0);// 旧属性全部设为非主键, 防止新建分类或更改主键后的多主键错误
				exsitAttrMap.put(proName, ccCiAttrDef);
			}
			orderNo = oldAttrDefs.size() + 1;
		}
		Set<String> exsitProNameSets = exsitAttrMap.keySet();
		// 属性定义验重
		List<String> uploadedAttrProNames = new ArrayList<String>(attrDefs.size());
		if (!BinaryUtils.isEmpty(attrDefs)) {
			for (CcCiAttrDef ccCiAttrDef : attrDefs) {
				String proName = ccCiAttrDef.getProName();
				// 上传空属性报错
				MessageUtil.checkEmpty(proName, "proName");
				// 上传重复属性报错
				if (uploadedAttrProNames.contains(proName)) MessageUtil.throwVerify(VerifyType.DUPLICATE, "proName", proName);
				if (!proName.equalsIgnoreCase(fixMapping.getNmCiCode())) {
					// 非主键属性判断 isMajor 的值
					if (1 == ccCiAttrDef.getIsMajor()) {
						throw MessageException.i18n("BS_MNAME_DUPLICATEMAJOR");
					}
				}else {
					ccCiAttrDef.setIsMajor(1);
				}
				// 添加本次上传已有属性名
				uploadedAttrProNames.add(proName);
				// 修改旧属性定义
				if (exsitProNameSets.contains(proName)) {
					Integer pastOrderNo = exsitAttrMap.get(proName).getOrderNo();
					if (BinaryUtils.isEmpty(pastOrderNo)) {
						//如果旧属性没有orderNo
						++orderNo;
						ccCiAttrDef.setOrderNo(orderNo);
					}else {
						//旧属性有orderNo
						ccCiAttrDef.setOrderNo(pastOrderNo);
					}
					exsitAttrMap.put(proName, ccCiAttrDef);
					continue;
				}
				// 为新增属性添加排序
				orderNo++;
				ccCiAttrDef.setOrderNo(orderNo);
				exsitAttrMap.put(proName, ccCiAttrDef);
			}
		}
		List<CcCiAttrDef> completeAttrDefs = new ArrayList<CcCiAttrDef>(exsitAttrMap.values());
		ciClassInfo.setAttrDefs(completeAttrDefs);
		return ciClassInfo;
	}


	/**
	 * 确定同一ci分类的sheet页中 那页是全量更新
	 * @param cdts
	 * @return
	 */
	public static int findOverrideIndex(List<CiExcelCdt> cdts) {
		MessageUtil.checkEmpty(cdts, "ciExcelSaveCdts.cdts");
		// 当有多页时,先遍历一遍,有全量的先记录索引后break
		for (int i = 0; i < cdts.size(); i++) {
			CiExcelCdt ciExcelCdt = cdts.get(i);
			if (CiExcelCdt.IMPORT_TYPE_OVERRIDE.equals(ciExcelCdt.getImportType())) return i;
		}
		// 没有全量更新返回0
		return 0;
	}

	public static boolean isOverride(List<CiExcelCdt> cdts) {
		MessageUtil.checkEmpty(cdts, "ciExcelSaveCdts.cdts");
		for (int i = 0; i < cdts.size(); i++) {
			CiExcelCdt ciExcelCdt = cdts.get(i);
			if (CiExcelCdt.IMPORT_TYPE_OVERRIDE.equals(ciExcelCdt.getImportType()))
				return true;
		}
		return false;
	}
	
	public static Workbook getWorkbook(String userCode, String fileName) {
		
		File file = getFile(userCode, fileName);
		Workbook workbook = getWorkbook(file);
		return workbook;
	}
}	
